set hive.exec.dynamic.partition=true;            --  动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=500;
insert overwrite table jms_dm.dm_end_dispatch_store_level_cnt_dt
select   
      max(agent_code        ) as agent_code       --代理区编码  
    , max(agent_name        ) as agent_name       --代理区名称
    , max(franchisee_code   ) as franchisee_code  --加盟商code
    , max(franchisee_name   ) as franchisee_name  --加盟商name
    , max(virt_code         ) as virt_code        --虚拟代理区code
    , max(virt_name         ) as virt_name        --虚拟代理区name
    , network_code                                --网点编码
    , max(network_name      ) as network_name     --网点名称
    , score_level                                 --等级
    , count(distinct store_code) as store_cnt                       --驿站数量
    , cast(sum(store_score)/count(distinct store_code)/9 as decimal(16,2))  as score_avg --平均得分 5是指5个指标
    , dt as date_time
    , business_id
    , max(business_name) as business_name
    , dt
from jms_dm.dm_end_dispatch_store_level_detail_dt
where dt='{{ execution_date | cst_ds }}'
group by network_code,score_level,dt,business_id
distribute by 1
;
